package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import entity.LabReservation;
import entity.SchoolClass;
import util.JDBCUtil;

public class LabReservationDAO {
    // 检查时间段冲突
    public boolean checkTimeConflict(int labId, Date startTime, Date endTime) {
        String sql = "SELECT COUNT(*) FROM lab_reservation " +
                "WHERE lab_id = ? " +
                "AND (start_time < ? AND end_time > ?)";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, labId);
            pstmt.setTimestamp(2, new Timestamp(endTime.getTime()));
            pstmt.setTimestamp(3, new Timestamp(startTime.getTime()));
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            return rs.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private boolean isWithinAvailableTime(int labId, Date startTime, Date endTime) {
        String sql = "SELECT COUNT(*) FROM lab_available_time " +
                "WHERE lab_id = ? " +
                "AND start_time <= ? " +
                "AND end_time >= ?";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, labId);
            pstmt.setTimestamp(2, new Timestamp(startTime.getTime()));
            pstmt.setTimestamp(3, new Timestamp(endTime.getTime()));
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            return rs.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 添加预约
    public boolean addReservation(LabReservation reservation) {

        if (!isWithinAvailableTime(reservation.getLabId(), reservation.getStartTime(), reservation.getEndTime())) {
            System.out.println("预约时间段不在实验室可用时间段内");
            return false;
        }
        String sql = "INSERT INTO lab_reservation (lab_id, user_id, start_time, end_time, purpose) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, reservation.getLabId());
            pstmt.setInt(2, reservation.getUserId());
            pstmt.setTimestamp(3, new Timestamp(reservation.getStartTime().getTime()));
            pstmt.setTimestamp(4, new Timestamp(reservation.getEndTime().getTime()));
            pstmt.setString(5, reservation.getPurpose());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 获取实验室的所有预约记录
    public List<LabReservation> getReservationsByLab(int labId) {
        List<LabReservation> reservations = new ArrayList<>();
        String sql = "SELECT * FROM lab_reservation WHERE lab_id = ?";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, labId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                reservations.add(new LabReservation(
                        rs.getInt("reservation_id"),
                        rs.getInt("lab_id"),
                        rs.getInt("user_id"),
                        rs.getTimestamp("start_time"),
                        rs.getTimestamp("end_time"),
                        rs.getString("purpose"),
                        rs.getTimestamp("create_time")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reservations;
    }

    // 获取用户的所有预约记录
    public List<LabReservation> getReservationsByUser(int userId) {
        List<LabReservation> reservations = new ArrayList<>();
        String sql = "SELECT * FROM lab_reservation WHERE user_id = ?";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                reservations.add(new LabReservation(
                        rs.getInt("reservation_id"),
                        rs.getInt("lab_id"),
                        rs.getInt("user_id"),
                        rs.getTimestamp("start_time"),
                        rs.getTimestamp("end_time"),
                        rs.getString("purpose"),
                        rs.getTimestamp("create_time")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reservations;
    }

    /**
     * 检查学生是否可以预约该实验室（老师是否正在预约该实验室）
     */
    public boolean canStudentReserveLab(int labId, int studentId, Date startTime, Date endTime) {
        // 1. 获取学生所在班级
        ClassDAO classDAO = new ClassDAO();
        SchoolClass studentClass = classDAO.getClassByStudentId(studentId);
        if (studentClass == null) {
            System.out.println("学生不属于任何班级");
            return false;
        }

        // 2. 检查该班级的老师是否正在预约该实验室
        String sql = "SELECT COUNT(*) FROM lab_reservation " +
                "WHERE lab_id = ? AND user_id = ? " +
                "AND ((start_time BETWEEN ? AND ?) OR (end_time BETWEEN ? AND ?) " +
                "OR (? BETWEEN start_time AND end_time) OR (? BETWEEN start_time AND end_time))";
        try (Connection conn = JDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, labId);
            pstmt.setInt(2, studentClass.getTeacherId());
            Timestamp startTimestamp = new Timestamp(startTime.getTime());
            Timestamp endTimestamp = new Timestamp(endTime.getTime());
            pstmt.setTimestamp(3, startTimestamp);
            pstmt.setTimestamp(4, endTimestamp);
            pstmt.setTimestamp(5, startTimestamp);
            pstmt.setTimestamp(6, endTimestamp);
            pstmt.setTimestamp(7, startTimestamp);
            pstmt.setTimestamp(8, endTimestamp);

            ResultSet rs = pstmt.executeQuery();
            rs.next();
            return rs.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
